# coding: utf-8

"""
transutils.database, the infomation caching database of transutils.

This module provides a SQLite database that caches all the relevant information
extracted from the translated message catalogs, reducing the time needed to
load the information, as well as enabling easier queries with SQL the language.
"""

import os
import os.path
import sqlite3

import polib

def _load_or_initialize_database(db_name):
    db = sqlite3.connect(db_name)
    db.executescript(u"""
    CREATE TABLE IF NOT EXISTS files (
        file_id INTEGER PRIMARY KEY,
        alias VARCHAR NOT NULL UNIQUE,
        modified_time INTEGER NOT NULL,
        pot_date VARCHAR NOT NULL,
        po_date VARCHAR NOT NULL
    );
    
    CREATE TABLE IF NOT EXISTS entries (
        entry_id INTEGER PRIMARY KEY,
        message VARCHAR NOT NULL,
        message_plural VARCHAR NOT NULL
        tag VARCHAR NOT NULL,
        file_id INTEGER NOT NULL,
        FOREIGN KEY(file_id) REFERENCES files(file_id) ON DELETE CASCADE
    );
    
    CREATE TABLE IF NOT EXISTS translations (
        translation_id INTEGER PRIMARY KEY,
        entry_id INTEGER NOT NULL,
        index INTEGER NOT NULL,
        text VARCHAR NOT NULL,
        FOREIGN KEY(entry_id) REFERENCES entries(entry_id) ON DELETE CASCADE
    );
    """)
    return db

def _get_pofile_when_sync_required(cursor, alias, path):
    cursor.execute(u"SELECT modified_time, pot_date, po_date FROM files WHERE alias = ?", alias)
    data = cursor.fetchone()
    
    # This catalog is not cached at all, sync required.
    if data is None:
        pofile = polib.pofile(path, autodetect_encoding=False, encoding='utf-8')
        return pofile
    
    # Is the catalog file modified since the last sync?
    old_mtime = data[0]
    new_mtime = int(os.path.getmtime(path))
    if (new_mtime == old_mtime):
        return None
    
    # The file itself is modified, but the translations may stay intact,
    # e.g. after the file is reformatted with msgattrib.
    # Here we assume that "relevant" modifications are always indicated by
    # the change of the POT-Creation-Date and/or PO-Revision-Date headers.
    # After all, translators are expected to update the headers, no? ;)
    
    pofile = polib.pofile(path, autodetect_encoding=False, encoding='utf-8')
    
    old_pot_date = data[1]
    old_po_date = data[2]
    new_pot_date = pofile.metadata.get(u'POT-Creation-Date')
    new_po_date = pofile.metadata.get(u'PO-Revision-Date')
    
    if (old_pot_date != new_pot_date) or (old_po_date != new_po_date):
        return pofile
    else:
        return None

def _synchronize(cursor, alias, pofile):
    # The outdated cache of the speicified file is to be discarded.
    # All cached entries from this file are also deleted due to cascading.
    cursor.execute(u"DELETE FROM files WHERE alias = ?", alias)
    cursor.commit()
    
    # Cache the catalog's metadata.
    mod_time = int(os.path.getmtime(path))
    pot_date = pofile.metadata.get(u'POT-Creation-Date')
    po_date = pofile.metadata.get(u'PO-Revision-Date')
    cursor.execute(u"INSERT INTO files VALUES(NULL, ?, ?, ?, ?)",
                   (alias, modified_time, pot_date, po_date))
    cursor.commit()               
    
    cursor.execute("SELECT last_insert_rowid()")
    file_id = cursor.fetchone()[0]
    
    # Cache the entries
    for entry in pofile:
        # In wesnoth, msgids are sometimes tagged with a 'context' by '^',
        # while gettext's native context support is not used.
        # So the 'actual' message may not be the plain msgid.
        splitted_msgid = entry.msgid.split(u'^')
        message = splitted_msgid[0]
        tag = None
        if len(splitted_msgid) == 2:
            tag = message
            message = splitted_msgid[1]
        
        # For translation strings, cache the first one only,
        # but those messages with plural forms will be recorded as such.
        translation = entry.msgstr
        has_plural = entry.msgstr_plural is not None
        if has_plural: translation = entry.msgstr_plural[0]
        
        cursor.execute(u"INSERT INTO entries VALUES(NULL, ?, ?, ?, ?, ?)",
                        (message, tag, file_id, translation, has_plural))
    cursor.commit()

def _ensure_synchronized(cursor, alias, path):
    pofile = _get_pofile_when_sync_required(db, alias, path)
    if pofile is None: return
    _synchronize(cursor, alias, pofile)

class Database(object):
    def __init__(self):
        self._db = _load_or_initialize_database(u"transutils.cache")
        cursor = db.cursor()
        for (alias, path) in _find_catalogs(u".").iteritems():
            _ensure_synchronized(cursor, alias, path)
            
    def cursor(self):
        return self._db.cursor()
    
    def close(self):
        self._db.close()
        
